package com.ilas.sql;

import lombok.extern.slf4j.Slf4j;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.util.Properties;

/**
 * <p>导入sql文件到数据库</p>
 *
 * @author luzck
 * @date 2021/4/1 16:43
 */
@Slf4j
public class ScriptSql {

    private static final String SCRIPT_SQL = "classpath:tenant_base.sql";

    public static void exeSQLScript(Properties prop) throws Exception {
        //读取sql脚本文件保存到数组中
        String[] sqlName = {SCRIPT_SQL};
        //循环执行sql脚本
        for (String s : sqlName) {
            //利用 Ant 的SQL Task来实现执行SQL 脚本的功能。ant 包中的 SQLExec类的扩展，此时需要将ant 包(ant.jar)导入
            SQLExec sqlExec = new SQLExec();
            // mysql8.x数据库驱动：com.mysql.cj.jdbc.Driver
            // mysql5.x数据库驱动：com.mysql.jdbc.Driver
            String mysqlDriver = prop.getProperty("driver", "com.mysql.cj.jdbc.Driver");
            String url = prop.getProperty("url");
            String username = prop.getProperty("username", "root");
            String password = prop.getProperty("password", "root");

            // 设置数据库参数
            sqlExec.setDriver(mysqlDriver);
            sqlExec.setUrl(url);
            sqlExec.setUserid(username);
            sqlExec.setPassword(password);
            //防止乱码，sql文件也需要设置为utf-8
            sqlExec.setEncoding("UTF8");
            //设置sql脚本
            File file = ResourceUtils.getFile(s);
            sqlExec.setSrc(file);
            // 设置是否输出 true-打印,false-不打印
            sqlExec.setPrint(false);
            // 要指定这个属性，不然会出错
            sqlExec.setProject(new Project());
            sqlExec.setOutputEncoding("UTF8");
            sqlExec.setAutocommit(true);
            sqlExec.execute();
            log.info("sqlExec execute success!");
        }
    }

    public static void main(String[] args) {
        Properties prop = new Properties();
        prop.setProperty("url",  "jdbc:mysql://127.0.0.1:3306/test001?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=Asia/Shanghai");
        prop.setProperty("username", "root");
        prop.setProperty("password", "123456");
        try {
            ScriptSql.exeSQLScript(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
